#
# @author Wójcik Karol <wojcikk@student.agh.edu.pl>
# @licence http://www.gnu.org/copyleft/gpl.html GNU/GPL
#

class SubjectsReportController < ApplicationController
layout 'standard'
before_filter :login_required

  def index
	s=nil
	@fac=Faculty.find(:all)
	if params[:facultie] !=nil
		m=Major.find(:all,:conditions=>['faculty_id=?',params[:facultie]])
		for n in m
			if s != nil
				s+=Subject.find(:all,:conditions=>['major_id=?',n.id])
			else
				s=Subject.find(:all,:conditions=>['major_id=?',n.id])
			end
		end
	else
		s=Subject.find(:all)
	end
	@out={}
	@names={}
	for i in s
		@names[i.id]=i.name
		if @out[i.id]==nil
			@out[i.id]={}
		end
		#@out<<Lesson.find(:all,:conditions => ["subject = ?",s[0]])
		less=Lesson.find(:all,:conditions => ["subject_id = ?",i.id])
		for l in less
			lu=LessonsUser.find(:all,:conditions => ["lesson_id = ?", l.id])
			sum=0
			
			@out[i.id][l.semestr]=lu
			
			p '---',i.name,l.semestr
			
		end
		#@out<<i
		
	end
	
	params[:columns]  ||= ["class_type","surname","name","lgrzaj","lgrroz","lgodznazaj","lgodzin","sums"]
	
    @columns = params['columns']
	
    @subjects = ClassType.find_by_sql("
  SELECT 
	subjects.id AS id,
    lessons.semestr AS semestr,
    majors.name AS major,
    subjects.name AS subject,
    subjects.year AS year,
    class_types.name AS class_type,
    users.surname AS surname,
    users.name AS name,
    marks.name AS mark,
    lessons_users.lgrroz * lessons.lgodznazaj * class_types.ratio AS lgodzin,
    lessons_users.lgrroz AS lgrroz,
    lessons_users.lgrzaj AS lgrzaj,
    lessons.lgodznazaj AS lgodznazaj
  FROM subjects
  LEFT JOIN lessons ON lessons.subject_id = subjects.id
  LEFT JOIN lessons_users ON lessons_users.lesson_id = lessons.id
  LEFT JOIN users ON lessons_users.user_id = users.id
  LEFT JOIN majors ON subjects.major_id = majors.id
  LEFT JOIN class_types ON class_types.id = lessons.class_type_id
  LEFT JOIN marks ON users.mark_id = marks.id
  ORDER BY major,subject,year,semestr DESC, class_type, surname, name
    ")

	@sums ||= ClassType.find_by_sql("
	SELECT 
		subjects.name as subject,
		class_types.name as class_type,
		SUM(lessons_users.lgrroz * lessons.lgodznazaj * class_types.ratio) as all
	FROM 
	    subjects
	LEFT JOIN lessons ON lessons.subject_id = subjects.id 
	LEFT JOIN class_types ON lessons.class_type_id = class_types.id
	LEFT JOIN lessons_users ON lessons_users.lesson_id = lessons.id
	GROUP BY subjects.name, class_types.name
	ORDER BY subjects.name, class_types.name
	") 

  end
end
